# -*-coding:utf-8-*-
import datetime
import time
from time import strftime

import pymysql
from config.config import NOW_ENV, TEST_DB_CONNECT, PRE_DB_CONNECT, PROD_DB_CONNECT
from core.YamlLoad import read_center_params
from utils.logger import log


# 数据库交互
class DB:
    def __init__(self, env=NOW_ENV):
        if env.lower() == "test":
            self.connections = TEST_DB_CONNECT
        elif env.lower() == "beta":
            self.connections = PRE_DB_CONNECT
        elif env.lower() == "soa":
            self.connections = PROD_DB_CONNECT
        else:
            log.error("数据库环境配置不正确！")
            raise
        self.db = None
        self.cursor = None

    def connect_db(self):
        host, port, user, password = self.connections
        select_max_num = 5
        result = False
        while select_max_num:
            try:
                self.db = pymysql.connect(host=host, port=port, user=user,
                                          password=password)
                self.db.timeout = 60
                self.cursor = self.db.cursor()
                log.info("数据库连接成功。 %s" % self.db.host_info)
                result = True
                break
            except Exception as e:
                log.error("数据库连接失败。错误:%s" % str(e))
                select_max_num -= 1
                time.sleep(1)
        if not result:
            raise Exception('数据库连接失败!')

    # 该方法适用于yaml文件和csv文件中，只能返回查询结果第一行的第一个值
    def execute_sql_first_value(self, sql):
        self.connect_db()
        log.info("执行sql语句:%s" % str(sql))
        try:
            self.cursor.execute(sql)
            result = self.cursor.fetchone()
            self.db.commit()
            log.info("SQL执行结果: %s" % str(result))
            return result[0]
        except Exception as s:
            # 回滚事务
            self.db.rollback()
            log.error("SQL执行报错，错误: %s" % str(s))
        finally:
            self.close_db()
            log.info("关闭数据库连接。")

    def execute_sql(self, sql):
        self.connect_db()
        log.info("执行sql语句:%s" % str(sql))
        try:
            self.cursor.execute(sql)
            result = self.cursor.fetchone()
            self.db.commit()
            log.info("SQL执行结果: %s" % str(result))
            return result
        except Exception as s:
            # 回滚事务
            self.db.rollback()
            log.error("SQL执行失败，错误信息: %s" % str(s))
        finally:
            self.close_db()
            log.info("关闭数据库连接。")

    def close_db(self):
        self.cursor.close()
        self.db.close()

    def load_db(self, data):
        if isinstance(data, str):
            if data:
                log.info("执行sql语句:%s" % data)
                value = self.execute_sql_first_value(data)
                log.info("sql语句执行的结果为:%s" % str(value))
            return value
        else:
            log.error("传入的数据格式有问题，请检查.")

    def get_login_code(self, phone, name, run_time):
        """获取验证码"""
        log.info("数据库查询，获取登录验证码")
        if name == 'b':
            timestamp = run_time
        elif name == 'h5':
            timestamp = run_time
        else:
            raise AttributeError('查询验证码终端参数错误...传入值为：%s' % name)

        if timestamp:
            dt = datetime.datetime.fromtimestamp(timestamp)
            formatted_time = dt.strftime("%Y-%m-%d %H:%M:%S")
            log.info("获取验证码时间为:" + formatted_time)
        else:
            log.error('没有获取验证码时间')
            raise AttributeError("没有时间戳")
        sql = "select REPLACE(JSON_EXTRACT(request_param, '$.code'), '\"', '') code,send_time from `edata-message`.t_sms_send_log where phone = %s and sms_type_id = 1 and template_code = 1 order by send_time desc limit 1" % phone
        self.connect_db()
        try:
            log.info("执行sql语句:%s" % str(sql))
            self.cursor.execute(sql)
            result = self.cursor.fetchone()
            log.info("SQL执行结果: %s" % str(result))
            code = result[0]
            db_send_time = result[1]
            send_time_format = db_send_time.strftime("%Y-%m-%d %H:%M:%S")
            db_send_timestamp = db_send_time.timestamp()
            result_dict = {'code': code, 'db_send_timestamp': db_send_timestamp, 'timestamp': timestamp}
            return result_dict
        except Exception as s:
            # 回滚事务
            self.db.rollback()
            log.error("SQL执行失败，错误信息: %s" % str(s))
        finally:
            self.close_db()
            log.info("关闭数据库连接。")


db = DB()
# print(db.get_login_code(15773899933))
# print(db.get_login_code('15773899933'))
# print(db.get_login_code(15773899933))
